<?php
/**
 * Created by PhpStorm.
 * User: 冯
 * Date: 2017/8/14
 * Time: 10:58
 */
function send_msg($tel= null,$msg = null){
    if ($msg && $tel){
        $url = "http://sms.10690221.com:9011/hy/";

        $auth = md5('yhwj'.'yhwj1234');
        $post_string = "uid=503831&auth=".$auth."&mobile=".$tel."&msg=".urlencode($msg)."&expid=0&encode=utf-8";

        $this_header = array("content-type: application/x-www-form-urlencoded;charset=UTF-8");
        $ch = curl_init();
        curl_setopt($ch, CURLOPT_URL, $url);
        curl_setopt($ch, CURLOPT_HTTPHEADER, $this_header);
        curl_setopt($ch, CURLOPT_POSTFIELDS, $post_string);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
        $data = curl_exec($ch);
        curl_close($ch);
        $arr = explode(',',$data);
        if($arr[0]==='0') {
            return true;
        }else{
            return false;
        }
    }else{
        return false;
    }


}
//参数1：访问的URL，参数2：post数据(不填则为GET)，参数3：提交的$cookies,参数4：是否返回$cookies
function curl_request($url,$post='',$cookie='', $returnCookie=0){
    $curl = curl_init();
    curl_setopt($curl, CURLOPT_URL, $url);
    curl_setopt($curl, CURLOPT_USERAGENT, 'Mozilla/5.0 (compatible; MSIE 10.0; Windows NT 6.1; Trident/6.0)');
    curl_setopt($curl, CURLOPT_FOLLOWLOCATION, 1);
    curl_setopt($curl, CURLOPT_AUTOREFERER, 1);
    curl_setopt($curl, CURLOPT_REFERER, "http://XXX");
    if($post) {
        curl_setopt($curl, CURLOPT_POST, 1);
        curl_setopt($curl, CURLOPT_POSTFIELDS, http_build_query($post));
    }
    if($cookie) {
        curl_setopt($curl, CURLOPT_COOKIE, $cookie);
    }
    curl_setopt($curl, CURLOPT_HEADER, $returnCookie);
    curl_setopt($curl, CURLOPT_TIMEOUT, 10);
    curl_setopt($curl, CURLOPT_RETURNTRANSFER, 1);
    $data = curl_exec($curl);
    if (curl_errno($curl)) {
        return curl_error($curl);
    }
    curl_close($curl);
    if($returnCookie){
        list($header, $body) = explode("\r\n\r\n", $data, 2);
        preg_match_all("/Set\-Cookie:([^;]*);/", $header, $matches);
        $info['cookie']  = substr($matches[1][0], 1);
        $info['content'] = $body;
        return $info;
    }else{
        return $data;
    }
}


//生成随机验证码
function generate_code($length = 6) {
    return rand(pow(10,($length-1)), pow(10,$length)-1);
}

function object2array(&$object) {
    $object =  json_decode( json_encode( $object),true);
    return  $object;
}
//加密函数

function md5_encrypt($code=null,$right_str=null){
    if ($code){
        return md5($code);
    }else{
        return false;
    }

}

function json_send($data=null){
    if (!$data){
        $data=['status'=>'fail','msg'=>'出错','data'=>''];
    }
    $data = json_decode(json_encode($data),TRUE);
    $data = _unsetNull($data);
    echo json_encode($data);
}

function _unsetNull($arr){
    if($arr !== null){
        if(is_array($arr)){
            if(!empty($arr)){
                foreach($arr as $key => $value){
                    if($value === null){
                        $arr[$key] = '';
                    }else{
                        $arr[$key] = _unsetNull($value);      //递归再去执行
                    }
                }
            }else{  }
        }else{
            if($arr === null){ $arr = ''; }         //注意三个等号
        }
    }else{ $arr = ''; }
    return $arr;
}

function changecontent($arr) {
    $result = array();
    if(is_array($arr)) {
        foreach($arr as $key=>$val) {
            if(is_array($val)) {
                $newarr = changecontent($val);
                $result[$key] = $newarr;
            } else if($val === NULL) {
                $result[$key] = '';
            } else {
                $result[$key] = $val;

            }
        }
    }
    return $result;
}

function md5_key(){
    return md5("wjyh2017");
}


function getNextMonthDays($date){
    $timestamp=strtotime($date);
    $arr=getdate($timestamp);
    if($arr['mon'] == 12){
        $year=$arr['year'] +1;
        $month=$arr['mon'] -11;
        $firstday=$year.'-0'.$month.'-01';
        $lastday=date('Y-m-d',strtotime("$firstday +1 month -1 day"));
    }else{
        $firstday=date('Y-m-01',strtotime(date('Y',$timestamp).'-'.(date('m',$timestamp)+1).'-01'));
        $lastday=date('Y-m-d',strtotime("$firstday +1 month -1 day"));
    }
    return array($firstday,$lastday);
}

function getthemonth($date)
{
    $firstday = date('Y-m-01', strtotime($date));
    $lastday = date('Y-m-d', strtotime("$firstday +1 month -1 day"));
    return array($firstday, $lastday);
}

/**
 * 正则表达式验证email格式
 *
 * @param string $str    所要验证的邮箱地址
 * @return boolean
 */
function is_email($str) {
    if (!$str) {
        return false;
    }
    return preg_match('#[a-z0-9&\-_.]+@[\w\-_]+([\w\-.]+)?\.[\w\-]+#is', $str) ? true : false;
}
/**
 * 用正则表达式验证手机号码(中国大陆区)
 * @param integer $num    所要验证的手机号
 * @return boolean
 */
 function is_mobile($num) {
    if (!$num) {
        return false;
    }
    return (preg_match("/^1[34578]{1}\d{9}$/",$num))?true:false;
}

/*
中文验证的方法
参数：$str,$num1,$num2
返回值:匹配成功返回匹配的次数
*/
 function checkChina($str,$num1,$num2=''){
    //正则
    $reg='/^[\x{4e00}-\x{9fa5}]{'.$num1.','.$num2.'}'.'$/u';
    return preg_match($reg,$str);
}

/*
验证身份证
参数 $card
返回值: 匹配成功返回匹配的次数
*/
 function checkCard($card){
    //正则
    $reg='/^(\d{18}|\d{17}x)$/';
    return preg_match($reg,$card);
}
/*
要求输入内容由数字、字母、下划线组成，不能以数字开头，有位数限制
参数：$str,$num1,$num2
返回值: 匹配成功返回匹配的次数
*/
 function checkStr($str,$num1,$num2){
    //正则
    $reg='/^[a-zA-Z_]\w{'.($num1-1).','.($num2-1).'}$/';
    return preg_match($reg,$str);
}
//只能是汉子和中文不能超过10位
function check_name($str){
    return preg_match("/^[\x{4e00}-\x{9fa5}A-Za-z]{1,10}$/u",$str);
}

// 校验唯一性
function check_one($obj,$table,$colum,$val)
{
    $sql = "SELECT
                   count(1) as re
                FROM
                    $table
                WHERE
                    $colum = $val ";
    $rs = $obj->query($sql)->row_array();
    if($rs['re'])
    {
        return false;
    }
    return true;
}


// 获取所有manager_bind 的父级ID
// 寻找所有上级
function find_all_father_id($obj,$id, &$res = ''){
    $sql = "SELECT
                    manager_id,
                    parent_id
                FROM
                    manager_bind
                WHERE
                    1 = 1
                AND manager_id = ".$id .
        " limit 1";
    $rs = $obj->query($sql)->row_array();
    if($rs){
        $res[] = $rs['parent_id'];
        find_all_father_id($obj,$rs['parent_id'],$res);
    }else{
        return $res;
    }

}

// bind_history 寻找所有上级 按月份
function find_all_father_id_month($obj,$id, $pay_time, &$res = ''){
    $sql = "SELECT
                    manager_id,
                    parent_id
                FROM
                    manager_bind_history
                WHERE
                    1 = 1
                AND manager_id = ".$id .
                " and start_time <= '$pay_time' and end_time > '$pay_time'".
        " limit 1";
    $rs = $obj->query($sql)->row_array();
    if($rs){
        $res[] = $rs['parent_id'];
        find_all_father_id($obj,$rs['parent_id'],$res);
    }else{
        return $res;
    }

}

// 寻找所有上级 函数
function find_all_parents($obj,$id)
{
    $sql = "SELECT * FROM manager_bind WHERE FIND_IN_SET(manager_id,queryFatherId($id));";
    $re = $obj->query($sql)->result_array();
    $res = '';
    if($re)
    {
        $res = array_column($re, 'parent_id');
    }
    return $res;

}

// 获取manager_bind 父级ID下面所有的小弟
function find_all_sun($obj,$id){
//    $sql1 = "DELIMITER $$
//DROP FUNCTION IF EXISTS queryChildrenId;
//CREATE FUNCTION queryChildrenId(mid INT)
//RETURNS VARCHAR(300)
//BEGIN
//DECLARE sTemp VARCHAR(4000);
//DECLARE sTempChd VARCHAR(4000);
//SET sTemp='$';
//SET sTempChd = CAST(mid AS CHAR);
//WHILE sTempChd IS NOT NULL DO
//SET sTemp= CONCAT(sTemp,',',sTempChd);
//SELECT GROUP_CONCAT(manager_id) INTO sTempChd FROM manager_bind WHERE FIND_IN_SET(parent_id,sTempChd)>0;
//END WHILE;
//RETURN sTemp;
//END$$
//DELIMITER ; ";
    $sql = "SELECT * FROM manager_bind WHERE FIND_IN_SET(manager_id,queryChildrenId($id)) AND manager_id <> $id;";
    $re = $obj->query($sql)->result_array();
    $res = '';
    if($re)
    {
        $res = array_column($re, 'manager_id');
        foreach ($res as $k => $v)
        {
            if($v == $id)
            {
                unset($res[$k]);
            }
        }
    }
    return $res;



}

// 检测是否需要降级(没有转正的)
function check_lowgrade($obj,$mid,$t1='',$t2='',$t3='',$t4='')
{
    if($t1 == '' || $t2 == '')
    {
        return false;
    }
    $t1 = "'".$t1."'";
    $t2 = "'".$t2."'";
    $t3 = "'".$t3."'";
    $t4 = "'".$t4."'";
    $sql = "CALL check_lowgrade($mid,$t1,$t2,$t3,$t4, @res, @msg)";
    $obj->query($sql);
    $result = $obj->query("select @res as res, @msg as msg")->row_array();
    if($result){
        return $result;
    }else{
        return false;
    }
}

// 检测是否需要降级(已经转正的)
function check_lowgrade_job($obj,$mid)
{
    $sql = "CALL check_lowgrade_job($mid, @res, @msg)";
    $obj->query($sql);
    $result = $obj->query("select @res as res, @msg as msg")->row_array();
    if($result){
        return $result;
    }else{
        return false;
    }
}


// 是否可以转正
function check_become($obj,$mid,$t1='',$t2='',$t3='',$t4=''){
    if($t1 == '' || $t2 == '')
    {
        return false;
    }
    $t1 = "'".$t1."'";
    $t2 = "'".$t2."'";
    $t3 = "'".$t3."'";
    $t4 = "'".$t4."'";
    $sql = "CALL check_become($mid,$t1,$t2,$t3,$t4, @res, @msg)";
    $obj->query($sql);
    $result = $obj->query("select @res as res, @msg as msg")->row_array();
    if($result){
        return $result;
    }else{
        return false;
    }

    /**
     * DELIMITER //
    DROP PROCEDURE IF EXISTS check_become;
    CREATE PROCEDURE check_become(
    IN  mid INT,
    IN  t1  VARCHAR(500),
    IN  t2  VARCHAR(500),
    IN  t3  VARCHAR(500),
    IN  t4  VARCHAR(500),
    OUT res INT,
    OUT msg VARCHAR(500)
    )
    BEGIN
    DECLARE identity VARCHAR(20);
    DECLARE first_month_performance DECIMAL(8, 2);
    DECLARE second_month_performance DECIMAL(8, 2);
    DECLARE third_month_performance DECIMAL(8, 2);

    # 初始化
    SET first_month_performance = 0;
    SET second_month_performance = 0;
    SET third_month_performance = 0;
    SET res = 0;
    SET msg = '不能转正';

    #身份
    SET res = 0;
    SELECT m.identity
    INTO identity
    FROM manager m
    WHERE m.id = mid;

    CASE identity

    WHEN '试合作销售经理'
    THEN
    SELECT ifnull(sum(a.money), 0)
    INTO first_month_performance
    FROM (SELECT *
    FROM push_money AS pm
    WHERE pm.manager_id = mid
    GROUP BY order_id
    UNION ALL SELECT *
    FROM push_money
    WHERE parent_id = mid) AS a
    WHERE a.created_at >= t2 AND a.created_at < t1;

    IF t3 <> ''
    THEN
    SELECT ifnull(sum(a.money), 0)
    INTO second_month_performance
    FROM (SELECT *
    FROM push_money AS pm
    WHERE pm.manager_id = mid
    GROUP BY order_id
    UNION ALL SELECT *
    FROM push_money
    WHERE parent_id = mid) AS a
    WHERE a.created_at >= t3 AND a.created_at < t2;
    END IF;

    IF t4 <> ''
    THEN
    SELECT ifnull(sum(a.money), 0)
    INTO third_month_performance
    FROM (SELECT *
    FROM push_money AS pm
    WHERE pm.manager_id = mid
    GROUP BY order_id
    UNION ALL SELECT *
    FROM push_money
    WHERE parent_id = mid) AS a
    WHERE a.created_at >= t4 AND a.created_at < t3;
    END IF;

    IF first_month_performance >= 25000 OR second_month_performance >= 25000 OR third_month_performance >= 25000
    THEN SET res = 1;
    SET msg = concat('单月满25000，可以直接转正。', '最近一个月：', first_month_performance, '最近第二个月：', second_month_performance,
    '最近第三个月：', third_month_performance);

    ELSEIF first_month_performance + second_month_performance >= 25000 OR
    second_month_performance + third_month_performance >= 25000
    THEN SET res = 1;
    SET msg = concat('连续两个月累加满25000，可以直接转正。', '最近一个月：', first_month_performance, '最近第二个月：',
    second_month_performance,
    '最近第三个月：', third_month_performance);

    ELSEIF first_month_performance + second_month_performance + third_month_performance >= 35000
    THEN SET res = 1;
    SET msg = concat('连续三个月累加满35000，可以直接转正。', '最近一个月：', first_month_performance, '最近第二个月：',
    second_month_performance,
    '最近第三个月：', third_month_performance);
    ELSE SET res = 0;
    END IF;

    WHEN '大客户经理'
    THEN
    SELECT ifnull(sum(score), 0)
    INTO first_month_performance
    FROM score AS s
    WHERE s.manager_id = mid AND s.created_at >= t2 AND s.created_at < t1;
    IF t3 <> ''
    THEN
    SELECT ifnull(sum(score), 0)
    INTO second_month_performance
    FROM score AS s
    WHERE s.manager_id = mid AND s.created_at >= t3 AND s.created_at < t2;
    END IF;
    IF t4 <> ''
    THEN
    SELECT ifnull(sum(score), 0)
    INTO third_month_performance
    FROM score AS s
    WHERE s.manager_id = mid AND s.created_at >= t4 AND s.created_at < t3;
    END IF;

    IF first_month_performance >= 200 OR second_month_performance >= 200 OR third_month_performance >= 200
    THEN SET res = 1;
    SET msg = concat('单月超200，可以转正', '最近一个月：', first_month_performance, '最近第二个月：',
    second_month_performance,
    '最近第三个月：', third_month_performance);
    ELSEIF first_month_performance >= 50 OR second_month_performance >= 70 OR third_month_performance >= 100
    THEN SET res = 1;
    SET msg = concat('可以转正', '最近一个月：', first_month_performance, '最近第二个月：',
    second_month_performance,
    '最近第三个月：', third_month_performance);
    ELSE SET res = 0;
    END IF;

    WHEN '资深大客户经理'
    THEN
    SELECT ifnull(sum(score), 0)
    INTO first_month_performance
    FROM score AS s
    WHERE s.manager_id = mid AND s.created_at >= t2 AND s.created_at < t1;
    IF t3 <> ''
    THEN
    SELECT ifnull(sum(score), 0)
    INTO second_month_performance
    FROM score AS s
    WHERE s.manager_id = mid AND s.created_at >= t3 AND s.created_at < t2;
    END IF;
    IF t4 <> ''
    THEN
    SELECT ifnull(sum(score), 0)
    INTO third_month_performance
    FROM score AS s
    WHERE s.manager_id = mid AND s.created_at >= t4 AND s.created_at < t3;
    END IF;

    IF first_month_performance >= 200 OR second_month_performance >= 200 OR third_month_performance >= 200
    THEN SET res = 1;
    SET msg = concat('单月超200，可以转正', '最近一个月：', first_month_performance, '最近第二个月：',
    second_month_performance,
    '最近第三个月：', third_month_performance);
    ELSEIF first_month_performance >= 60 OR second_month_performance >= 80 OR third_month_performance >= 100
    THEN SET res = 1;
    SET msg = concat('可以转正', '最近一个月：', first_month_performance, '最近第二个月：',
    second_month_performance,
    '最近第三个月：', third_month_performance);
    ELSE SET res = 0;
    END IF;

    WHEN '大区经理'
    THEN
    SELECT ifnull(sum(score), 0)
    INTO first_month_performance
    FROM score AS s
    WHERE s.manager_id = mid AND s.created_at >= t2 AND s.created_at < t1;
    IF t3 <> ''
    THEN
    SELECT ifnull(sum(score), 0)
    INTO second_month_performance
    FROM score AS s
    WHERE s.manager_id = mid AND s.created_at >= t3 AND s.created_at < t2;
    END IF;
    IF t4 <> ''
    THEN
    SELECT ifnull(sum(score), 0)
    INTO third_month_performance
    FROM score AS s
    WHERE s.manager_id = mid AND s.created_at >= t4 AND s.created_at < t3;
    END IF;

    IF first_month_performance >= 200 OR second_month_performance >= 200 OR third_month_performance >= 200
    THEN SET res = 1;
    SET msg = concat('单月超200，可以转正', '最近一个月：', first_month_performance, '最近第二个月：',
    second_month_performance,
    '最近第三个月：', third_month_performance);
    ELSEIF first_month_performance >= 60 OR second_month_performance >= 80 OR third_month_performance >= 100
    THEN SET res = 1;
    SET msg = concat('可以转正', '最近一个月：', first_month_performance, '最近第二个月：',
    second_month_performance,
    '最近第三个月：', third_month_performance);
    ELSE SET res = 0;
    END IF;

    WHEN '高级大区经理'
    THEN
    SELECT ifnull(sum(score), 0)
    INTO first_month_performance
    FROM score AS s
    WHERE s.manager_id = mid AND s.created_at >= t2 AND s.created_at < t1;
    IF t3 <> ''
    THEN
    SELECT ifnull(sum(score), 0)
    INTO second_month_performance
    FROM score AS s
    WHERE s.manager_id = mid AND s.created_at >= t3 AND s.created_at < t2;
    END IF;
    IF t4 <> ''
    THEN
    SELECT ifnull(sum(score), 0)
    INTO third_month_performance
    FROM score AS s
    WHERE s.manager_id = mid AND s.created_at >= t4 AND s.created_at < t3;
    END IF;

    IF first_month_performance >= 200 OR second_month_performance >= 200 OR third_month_performance >= 200
    THEN SET res = 1;
    SET msg = concat('单月超200，可以转正', '最近一个月：', first_month_performance, '最近第二个月：',
    second_month_performance,
    '最近第三个月：', third_month_performance);
    ELSEIF first_month_performance >= 60 OR second_month_performance >= 80 OR third_month_performance >= 100
    THEN SET res = 1;
    SET msg = concat('可以转正', '最近一个月：', first_month_performance, '最近第二个月：',
    second_month_performance,
    '最近第三个月：', third_month_performance);
    ELSE SET res = 0;
    END IF;


    WHEN '销售总监'
    THEN
    SELECT ifnull(sum(score), 0)
    INTO first_month_performance
    FROM score AS s
    WHERE s.manager_id = mid AND s.created_at >= t2 AND s.created_at < t1;
    IF t3 <> ''
    THEN
    SELECT ifnull(sum(score), 0)
    INTO second_month_performance
    FROM score AS s
    WHERE s.manager_id = mid AND s.created_at >= t3 AND s.created_at < t2;
    END IF;
    IF t4 <> ''
    THEN
    SELECT ifnull(sum(score), 0)
    INTO third_month_performance
    FROM score AS s
    WHERE s.manager_id = mid AND s.created_at >= t4 AND s.created_at < t3;
    END IF;

    IF first_month_performance >= 200 OR second_month_performance >= 200 OR third_month_performance >= 200
    THEN SET res = 1;
    SET msg = concat('单月超200，可以转正', '最近一个月：', first_month_performance, '最近第二个月：',
    second_month_performance,
    '最近第三个月：', third_month_performance);
    ELSEIF first_month_performance >= 60 OR second_month_performance >= 80 OR third_month_performance >= 100
    THEN SET res = 1;
    SET msg = concat('可以转正', '最近一个月：', first_month_performance, '最近第二个月：',
    second_month_performance,
    '最近第三个月：', third_month_performance);
    ELSE SET res = 0;
    END IF;

    ELSE SET res = 0;
    SET msg = '没有符合的身份';

    END CASE;
    END;
    DELIMITER ;

    CALL check_become(525, '2017-12-10', '2017-11-10', '2017-10-10', '2017-09-10', @res, @msg);

    SELECT
    @res AS res,
    @msg AS msg
     */
}

// 检测是否可以晋升
function check_upgrade($obj,$uid){

    $sql = "CALL check_upgrade($uid, @res, @msg)";
    $obj->query($sql);
    $result = $obj->query("select @res as res, @msg as msg")->row_array();
    if($result){
        return $result;
    }else{
        return false;
    }

    /**
     *
     * DELIMITER //
    DROP PROCEDURE IF EXISTS check_upgrade;
    CREATE PROCEDURE check_upgrade(
    IN  m_id INT,
    OUT res  INT,
    OUT msg  VARCHAR(500)
    )
    BEGIN
    DECLARE identity VARCHAR(20);
    DECLARE create_time DATETIME;
    DECLARE first_month CHAR(7);
    DECLARE second_month CHAR(7);
    DECLARE third_month CHAR(7);
    DECLARE four_month CHAR(7);
    DECLARE five_month CHAR(7);
    DECLARE six_month CHAR(7);
    DECLARE seven_month CHAR(7);
    DECLARE first_month_performance DECIMAL(8, 2);
    DECLARE second_month_performance DECIMAL(8, 2);
    DECLARE third_month_performance DECIMAL(8, 2);
    DECLARE four_month_performance DECIMAL(8, 2);
    DECLARE five_month_performance DECIMAL(8, 2);
    DECLARE six_month_performance DECIMAL(8, 2);
    DECLARE seven_month_performance DECIMAL(8, 2);
    DECLARE now_son INT; #当前的小弟

    # 初始化
    SET first_month_performance = 0;
    SET second_month_performance = 0;
    SET third_month_performance = 0;
    SET four_month_performance = 0;
    SET five_month_performance = 0;
    SET six_month_performance = 0;
    SET seven_month_performance = 0;

    #第一个月
    SET first_month = date_format(now(), '%Y-%c');
    #第二个月
    SET second_month = date_format(date_add(now(), INTERVAL -1 MONTH), '%Y-%c');
    #第三个月
    SET third_month = date_format(date_add(now(), INTERVAL -2 MONTH), '%Y-%c');
    #第四个月
    SET four_month = date_format(date_add(now(), INTERVAL -3 MONTH), '%Y-%c');
    #第五个月
    SET five_month = date_format(date_add(now(), INTERVAL -4 MONTH), '%Y-%c');
    #第六个月
    SET six_month = date_format(date_add(now(), INTERVAL -5 MONTH), '%Y-%c');
    #第七个月
    SET seven_month = date_format(date_add(now(), INTERVAL -6 MONTH), '%Y-%c');

    #身份
    SET res = 0;
    SELECT
    m.identity,
    m.created_at
    INTO identity, create_time
    FROM manager m
    WHERE m.id = m_id;

    CASE identity

    WHEN '合作销售经理'
    THEN
    SELECT ifnull(count(manager_id), 0)
    INTO now_son
    FROM manager_bind AS mb
    INNER JOIN manager AS m ON m.id = mb.manager_id
    WHERE mb.manager_id <> m_id AND FIND_IN_SET(mb.manager_id, queryChildrenId(m_id)) AND m.identity = '合作销售经理';

    SELECT ifnull(completed_performance, 0)
    INTO first_month_performance
    FROM norm n
    WHERE n.manager_id = m_id AND n.date = first_month
    LIMIT 1;

    SELECT ifnull(completed_performance, 0)
    INTO second_month_performance
    FROM norm n
    WHERE n.manager_id = m_id AND n.date = second_month
    LIMIT 1;

    IF now_son > 7 AND first_month_performance >= 20000 AND second_month_performance >= 20000
    THEN SET res = 1;
    SET msg = concat('可以申请晋升，本月业绩：', first_month_performance, '上个月：', second_month_performance, '辖下人数为：',
    now_son);
    ELSE
    SET res = 0;
    SET msg = concat('未满足条件，不能晋升，本月业绩：', first_month_performance, '上个月：', second_month_performance, '辖下人数为：',
    now_son);
    END IF;

    WHEN '特聘大客户经理'
    THEN
    SELECT ifnull(count(manager_id), 0)
    INTO now_son
    FROM manager_bind AS mb
    INNER JOIN manager AS m ON m.id = mb.manager_id
    WHERE mb.manager_id <> m_id AND FIND_IN_SET(mb.manager_id, queryChildrenId(m_id)) AND m.identity = '合作销售经理';


    SELECT ifnull(completed_performance, 0)
    INTO first_month_performance
    FROM norm n
    WHERE n.manager_id = m_id AND n.date = first_month
    LIMIT 1;

    SELECT ifnull(completed_performance, 0)
    INTO second_month_performance
    FROM norm n
    WHERE n.manager_id = m_id AND n.date = second_month
    LIMIT 1;

    IF now_son > 20 AND first_month_performance >= 50000 AND second_month_performance >= 50000
    THEN SET res = 1;
    SET msg = concat('可以申请晋升，本月业绩：', first_month_performance, '上个月：', second_month_performance, '辖下人数为：',
    now_son);
    ELSE
    SET res = 0;
    SET msg = concat('未满足条件，不能晋升，本月业绩：', first_month_performance, '上个月：', second_month_performance, '辖下人数为：',
    now_son);
    END IF;

    WHEN '资深大客户经理'
    THEN
    SELECT ifnull(count(manager_id), 0)
    INTO now_son
    FROM manager_bind AS mb
    INNER JOIN manager AS m ON m.id = mb.manager_id
    WHERE mb.manager_id <> m_id AND FIND_IN_SET(mb.manager_id, queryChildrenId(m_id)) AND m.identity = '合作销售经理';


    SELECT ifnull(completed_performance, 0)
    INTO first_month_performance
    FROM norm n
    WHERE n.manager_id = m_id AND n.date = first_month
    LIMIT 1;

    SELECT ifnull(completed_performance, 0)
    INTO second_month_performance
    FROM norm n
    WHERE n.manager_id = m_id AND n.date = second_month
    LIMIT 1;

    IF now_son > 30 AND first_month_performance >= 90000 AND second_month_performance >= 90000
    THEN SET res = 1;
    SET msg = concat('可以申请晋升，本月业绩：', first_month_performance, '上个月：', second_month_performance, '辖下人数为：',
    now_son);
    ELSE
    SET res = 0;
    SET msg = concat('未满足条件，不能晋升，本月业绩：', first_month_performance, '上个月：', second_month_performance, '辖下人数为：',
    now_son);
    END IF;

    WHEN '大客户经理'
    THEN
    SELECT ifnull(count(manager_id), 0)
    INTO now_son
    FROM manager_bind AS mb
    INNER JOIN manager AS m ON m.id = mb.manager_id
    WHERE mb.manager_id <> m_id AND FIND_IN_SET(mb.manager_id, queryChildrenId(m_id)) AND m.identity = '合作销售经理';


    SELECT ifnull(completed_performance, 0)
    INTO first_month_performance
    FROM norm n
    WHERE n.manager_id = m_id AND n.date = first_month
    LIMIT 1;

    SELECT ifnull(completed_performance, 0)
    INTO second_month_performance
    FROM norm n
    WHERE n.manager_id = m_id AND n.date = second_month
    LIMIT 1;

    IF now_son > 20 AND first_month_performance >= 60000 AND second_month_performance >= 60000
    THEN SET res = 1;
    SET msg = concat('可以申请晋升，本月业绩：', first_month_performance, '上个月：', second_month_performance, '辖下人数为：',
    now_son);
    ELSE
    SET res = 0;
    SET msg = concat('未满足条件，不能晋升，本月业绩：', first_month_performance, '上个月：', second_month_performance, '辖下人数为：',
    now_son);
    END IF;

    WHEN '高级大区经理'
    THEN
    SELECT ifnull(count(manager_id), 0)
    INTO now_son
    FROM manager_bind AS mb
    INNER JOIN manager AS m ON m.id = mb.manager_id
    WHERE mb.manager_id <> m_id AND FIND_IN_SET(mb.manager_id, queryChildrenId(m_id)) AND
    m.identity IN ('大客户经理', '资深大客户经理');


    SELECT ifnull(completed_performance, 0)
    INTO first_month_performance
    FROM norm n
    WHERE n.manager_id = m_id AND n.date = first_month
    LIMIT 1;

    SELECT ifnull(completed_performance, 0)
    INTO second_month_performance
    FROM norm n
    WHERE n.manager_id = m_id AND n.date = second_month
    LIMIT 1;

    SELECT ifnull(completed_performance, 0)
    INTO third_month_performance
    FROM norm n
    WHERE n.manager_id = m_id AND n.date = third_month
    LIMIT 1;

    SELECT ifnull(completed_performance, 0)
    INTO four_month_performance
    FROM norm n
    WHERE n.manager_id = m_id AND n.date = four_month
    LIMIT 1;

    SELECT ifnull(completed_performance, 0)
    INTO five_month_performance
    FROM norm n
    WHERE n.manager_id = m_id AND n.date = five_month
    LIMIT 1;

    SELECT ifnull(completed_performance, 0)
    INTO six_month_performance
    FROM norm n
    WHERE n.manager_id = m_id AND n.date = six_month
    LIMIT 1;

    SELECT ifnull(completed_performance, 0)
    INTO seven_month_performance
    FROM norm n
    WHERE n.manager_id = m_id AND n.date = seven_month
    LIMIT 1;

    #连续增长6个月
    IF (six_month_performance - seven_month_performance) / seven_month_performance >= 0.2 AND
    (five_month_performance - six_month_performance) / six_month_performance >= 0.2 AND
    (four_month_performance - five_month_performance) / five_month_performance >= 0.2 AND
    (third_month_performance - four_month_performance) / four_month_performance >= 0.2 AND
    (second_month_performance - third_month_performance) / third_month_performance >= 0.2 AND
    (first_month_performance - second_month_performance) / second_month_performance >= 0.2 AND
    now_son > 30

    THEN SET res = 1;
    SET msg = concat('可以申请晋升，六月业绩：', first_month_performance, '，', second_month_performance, '，',
    third_month_performance, '，', four_month_performance, '，', five_month_performance, '，',
    six_month_performance, '，', seven_month_performance, '，辖下人数：',
    now_son);
    ELSE
    SET res = 0;
    SET msg = concat('不能申请晋升，六月业绩：', first_month_performance, '，', second_month_performance, '，',
    third_month_performance, '，', four_month_performance, '，', five_month_performance, '，',
    six_month_performance, '，', seven_month_performance, '，辖下人数：',
    now_son);
    END IF;

    WHEN '大区经理'
    THEN
    SELECT ifnull(count(manager_id), 0)
    INTO now_son
    FROM manager_bind AS mb
    INNER JOIN manager AS m ON m.id = mb.manager_id
    WHERE mb.manager_id <> m_id AND FIND_IN_SET(mb.manager_id, queryChildrenId(m_id)) AND
    m.identity IN ('大客户经理', '资深大客户经理');


    SELECT ifnull(completed_performance, 0)
    INTO first_month_performance
    FROM norm n
    WHERE n.manager_id = m_id AND n.date = first_month
    LIMIT 1;

    SELECT ifnull(completed_performance, 0)
    INTO second_month_performance
    FROM norm n
    WHERE n.manager_id = m_id AND n.date = second_month
    LIMIT 1;

    SELECT ifnull(completed_performance, 0)
    INTO third_month_performance
    FROM norm n
    WHERE n.manager_id = m_id AND n.date = third_month
    LIMIT 1;

    SELECT ifnull(completed_performance, 0)
    INTO four_month_performance
    FROM norm n
    WHERE n.manager_id = m_id AND n.date = four_month
    LIMIT 1;

    SELECT ifnull(completed_performance, 0)
    INTO five_month_performance
    FROM norm n
    WHERE n.manager_id = m_id AND n.date = five_month
    LIMIT 1;

    SELECT ifnull(completed_performance, 0)
    INTO six_month_performance
    FROM norm n
    WHERE n.manager_id = m_id AND n.date = six_month
    LIMIT 1;

    SELECT ifnull(completed_performance, 0)
    INTO seven_month_performance
    FROM norm n
    WHERE n.manager_id = m_id AND n.date = seven_month
    LIMIT 1;

    #连续增长6个月
    IF (six_month_performance - seven_month_performance) / seven_month_performance >= 0.2 AND
    (five_month_performance - six_month_performance) / six_month_performance >= 0.2 AND
    (four_month_performance - five_month_performance) / five_month_performance >= 0.2 AND
    (third_month_performance - four_month_performance) / four_month_performance >= 0.2 AND
    (second_month_performance - third_month_performance) / third_month_performance >= 0.2 AND
    (first_month_performance - second_month_performance) / second_month_performance >= 0.2 AND
    now_son >= 30

    THEN SET res = 1;
    SET msg = concat('可以申请晋升，六月业绩：', first_month_performance, '，', second_month_performance, '，',
    third_month_performance, '，', four_month_performance, '，', five_month_performance, '，',
    six_month_performance, '，', seven_month_performance, '，辖下人数：',
    now_son);
    ELSE
    SET res = 0;
    SET msg = concat('不能申请晋升，六月业绩：', first_month_performance, '，', second_month_performance, '，',
    third_month_performance, '，', four_month_performance, '，', five_month_performance, '，',
    six_month_performance, '，', seven_month_performance, '，辖下人数：',
    now_son);
    END IF;

    ELSE SET res = 0;
    SET msg = '没有符合的身份，查询失败';

    END CASE;
    END;
    DELIMITER ;

     */
}

// 晋升路线
function next_identity($now_identity)
{
    $res = '';
    $push_money = 0;
    switch ($now_identity)
    {
        case '合作销售经理' :
            $res = '特聘大客户经理';
            break;
        case '特聘大客户经理' :
            $res = '大区经理';
            break;
        case '资深大客户经理' :
            $res = '大区经理';
            $push_money = 10000;
            break;
        case '大客户经理' :
            $res = '资深大客户经理';
            $push_money = 3000;
            break;
        case '大区经理' :
            $res = '高级大区经理';
            break;
        case  '高级大区经理' :
            $res = '销售总监';
            break;
        default :
            break;
    }

    return [$res,$push_money];

}


// 转正路线
function become_identity($now_identity)
{
    if($now_identity == '试合作销售经理')
    {
        $res = '合作销售经理';
    }else{
        $res = $now_identity;
    }
    return $res;
}

// 降级路线
function low_identity($identity){
    $res = '';
    switch ($identity){
        case '试合作销售经理' :
            $res = '试合作销售经理';
            break;
        case '合作销售经理':
            $res = '试合作销售经理';
            break;
        case '特聘大客户经理':
            $res = '试合作销售经理';
            break;
        case '大客户经理':
            $res = '合作销售经理';
            break;
        case '资深大客户经理':
            $res = '大客户经理';
            break;
        case '大区经理':
            $res = '资深大客户经理';
            break;
        case '高级大区经理':
            $res = '资深大客户经理';
            break;
        case '销售总监':
            $res = '大区经理';
            break;
    }
    return $res;
}

// 评分是否达标
function up_to_standard($identity,$score,$order)
{
    $res['status'] = 0;
    $res['target'] = 0;
    switch ($identity){
        case '资深大客户经理' :
            switch ($order) {
                case 1 :
                    if($score >= 60)
                    {
                        $res['status'] = 1;
                    }
                    $res['target'] = 60;
                    break;
                case 2 :
                    if($score >= 80)
                    {
                        $res['status'] = 1;
                    }
                    $res['target'] = 80;
                    break;
                case 3 :
                    if($score >= 100)
                    {
                        $res['status'] = 1;
                    }
                    $res['target'] = 100;
                    break;
            }
            break;
        case "大客户经理" :
            switch ($order) {
                case 1 :
                    if($score >= 50)
                    {
                        $res['status'] = 1;
                    }
                    $res['target'] = 50;
                    break;
                case 2 :
                    if($score >= 70)
                    {
                        $res['status'] = 1;
                    }
                    $res['target'] = 70;
                    break;
                case 3 :
                    if($score >= 100)
                    {
                        $res['status'] = 1;
                    }
                    $res['target'] = 100;
                    break;
            }
            break;
        case "大区经理" :
            switch ($order) {
                case 1 :
                    if($score >= 60)
                    {
                        $res['status'] = 1;
                    }
                    $res['target'] = 60;
                    break;
                case 2 :
                    if($score >= 80)
                    {
                        $res['status'] = 1;
                    }
                    $res['target'] = 80;
                    break;
                case 3 :
                    if($score >= 100)
                    {
                        $res['status'] = 1;
                    }
                    $res['target'] = 100;
                    break;
            }
            break;
        case "高级大区经理" :
            switch ($order) {
                case 1 :
                    if($score >= 60)
                    {
                        $res['status'] = 1;
                    }
                    $res['target'] = 60;
                    break;
                case 2 :
                    if($score >= 80)
                    {
                        $res['status'] = 1;
                    }
                    $res['target'] = 80;
                    break;
                case 3 :
                    if($score >= 100)
                    {
                        $res['status'] = 1;
                    }
                    $res['target'] = 100;
                    break;
            }
            break;
        case "销售总监" :
            switch ($order) {
                case 1 :
                    if($score >= 60)
                    {
                        $res['status'] = 1;
                    }
                    $res['target'] = 60;
                    break;
                case 2 :
                    if($score >= 80)
                    {
                        $res['status'] = 1;
                    }
                    $res['target'] = 80;
                    break;
                case 3 :
                    if($score >= 100)
                    {
                        $res['status'] = 1;
                    }
                    $res['target'] = 100;
                    break;
            }
            break;
    }
    return $res;
}

function getFirstCharter($str)
{
    if (empty($str)) {
        return '';
    }

    $fchar = ord($str{0});

    if ($fchar >= ord('A') && $fchar <= ord('z'))
        return strtoupper($str{0});

    $s1 = iconv('UTF-8', 'gb2312', $str);

    $s2 = iconv('gb2312', 'UTF-8', $s1);

    $s = $s2 == $str ? $s1 : $str;

    $asc = ord($s{0}) * 256 + ord($s{1}) - 65536;

    if ($asc >= -20319 && $asc <= -20284)
        return 'A';

    if ($asc >= -20283 && $asc <= -19776)
        return 'B';

    if ($asc >= -19775 && $asc <= -19219)
        return 'C';

    if ($asc >= -19218 && $asc <= -18711)
        return 'D';

    if ($asc >= -18710 && $asc <= -18527)
        return 'E';

    if ($asc >= -18526 && $asc <= -18240)
        return 'F';

    if ($asc >= -18239 && $asc <= -17923)
        return 'G';

    if ($asc >= -17922 && $asc <= -17418)
        return 'H';

    if ($asc >= -17417 && $asc <= -16475)
        return 'J';

    if ($asc >= -16474 && $asc <= -16213)
        return 'K';

    if ($asc >= -16212 && $asc <= -15641)
        return 'L';

    if ($asc >= -15640 && $asc <= -15166)
        return 'M';

    if ($asc >= -15165 && $asc <= -14923)
        return 'N';

    if ($asc >= -14922 && $asc <= -14915)
        return 'O';

    if ($asc >= -14914 && $asc <= -14631)
        return 'P';

    if ($asc >= -14630 && $asc <= -14150)
        return 'Q';

    if ($asc >= -14149 && $asc <= -14091)
        return 'R';

    if ($asc >= -14090 && $asc <= -13319)
        return 'S';

    if ($asc >= -13318 && $asc <= -12839)
        return 'T';

    if ($asc >= -12838 && $asc <= -12557)
        return 'W';

    if ($asc >= -12556 && $asc <= -11848)
        return 'X';

    if ($asc >= -11847 && $asc <= -11056)
        return 'Y';

    if ($asc >= -11055 && $asc <= -10247)
        return 'Z';

    return null;

}


function identify_order($in){
    $identify = array("销售经理","大客户经理|城市经理","大区经理","销售总监");
    if(preg_match("/$identify[0]/",$in))
    {
        return 1;
    }
    else if (preg_match("/$identify[1]/",$in))
    {
        return 2;
    }
    else if (preg_match("/$identify[2]/",$in))
    {
        return 3;
    }
    else if (preg_match("/$identify[3]/",$in))
    {
        return 4;
    }else{
        return 0;
    }
}

// 每月达标金额
function month_norm($obj,$mid)
{
    $sql = "SELECT identity,job_date from manager WHERE id = ".$mid;
    $rs = $obj->query($sql)->row_array();
    $identity = $rs['identity'];
    $job_date = $rs['job_date'];
    $last_month = date("Y-m",strtotime("- 1 month"));
    $res = 0;
    switch ($identity){
        case '试合作销售经理' :
            $res = 25000;
            break;
        case '合作销售经理':
            $res = 20000;
            break;
        case '特聘大客户经理':
            $res = 50000;
            break;
        case '大客户经理':
            if($job_date){
                $res = 60000;
            }else{
                $res = 0;
            }
            break;
        case '资深大客户经理':
            if($job_date){
                $res = 90000;
            }else{
                $res = 0;
            }
            break;
        case '大区经理':
            if($job_date){
                // 上个月的增加20%
                $sq = "     SELECT ifnull(sum(completed_performance),0) AS tmoney FROM norm n WHERE n.manager_id = $mid AND n.date = '$last_month'";
                $rs = $obj->query($sq)->row_array();
                $res = $rs['tmoney']*1.2;
            }else{
                $res = 0;
            }
            break;
        case '高级大区经理':
            if($job_date){
                // 上个月的增加20%
                $sq = "     SELECT ifnull(sum(completed_performance),0) AS tmoney FROM norm n WHERE n.manager_id = $mid AND n.date = '$last_month'";
                $rs = $obj->query($sq)->row_array();
                $res = $rs['tmoney']*1.2;
            }else{
                $res = 0;
            }
            break;
        case '销售总监':
            if($job_date){
                // 上个月的增加20%
                $sq = "     SELECT ifnull(sum(completed_performance),0) AS tmoney FROM norm n WHERE n.manager_id = $mid AND n.date = '$last_month'";
                $rs = $obj->query($sq)->row_array();
                $res = $rs['tmoney']*1.2;
            }else{
                $res = 0;
            }
            break;
    }
    return $res;

}

// 是否要判断晋升 转正后时间满足
function is_upgrade($obj,$mid){
    $sql = "SELECT identity,job_date from manager WHERE id = ".$mid;
    $rs = $obj->query($sql)->row_array();
    $identity = $rs['identity'];
    $job_date = date('Y-m-d',strtotime($rs['job_date']));
    $ed = getMonthNum($job_date,date('Y-m-d',time()));
    $res = 1;
    switch ($identity){
        case '合作销售经理':
            $res = 2-$ed;
            break;
        case '特聘大客户经理':
            $res = 2-$ed;
            break;
        case '大客户经理':
            $res = 2-$ed;
            break;
        case '资深大客户经理':
            $res = 2-$ed;
            break;
        case '大区经理':
            $res = 6-$ed;
            break;
        case '高级大区经理':
            $res = 6-$ed;
            break;
    }
    if($res <= 0)
    {
        return true;
    }else{
        return false;
    }

}

// 是否要判断降级 降级后时间满足
function is_lowgrade($obj,$mid){
    $sql = "SELECT identity,job_date from manager WHERE id = ".$mid;
    $rs = $obj->query($sql)->row_array();
    $identity = $rs['identity'];
    $job_date = date('Y-m-d',strtotime($rs['job_date']));
    $ed = getMonthNum($job_date,date('Y-m-d',time()));
    $res = 1;
    switch ($identity){
        case '合作销售经理':
            $res = 2-$ed;
            break;
        case '特聘大客户经理':
            $res = 2-$ed;
            break;
        case '大客户经理':
            $res = 2-$ed;
            break;
        case '资深大客户经理':
            $res = 2-$ed;
            break;
        case '大区经理':
            $res = 2-$ed;
            break;
        case '高级大区经理':
            $res = 3-$ed;
            break;
        case '销售总监':
            $res = 3-$ed;
            break;
    }
    if($res <= 0)
    {
        return true;
    }else{
        return false;
    }

}

function getMonthNum($date1,$date2){
    $date1_stamp=strtotime($date1);
    $date2_stamp=strtotime($date2);
    list($date_1['y'],$date_1['m'])=explode("-",date('Y-m',$date1_stamp));
    list($date_2['y'],$date_2['m'])=explode("-",date('Y-m',$date2_stamp));
    return abs($date_1['y']-$date_2['y'])*12 +$date_2['m']-$date_1['m'];
}

function array_group_by($arr, $key)
{
    $grouped = [];
    foreach ($arr as $value) {
        $sk = $value;
        unset($value[$key]);
        $grouped[$sk[$key]] = $value;
    }
    if (func_num_args() > 2) {
        $args = func_get_args();
        foreach ($grouped as $key => $value) {
            array_group_by([$value],array_slice($args, 2, func_num_args()));
        }
    }
    return $grouped;
}




